﻿using BwTMusicModels;
using HelperModels;
using HelperModels.Singer;
using HwjUIDI.Utility.DatabaseHelper;
using System;
using System.Collections.Generic;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Text;
using System.Threading.Tasks;

namespace BwTServices.Singer
{
    public class SingerServices
    {
        public List<SingerHelper> GetAllSingerList(SingerFilter filter, out int total)
        {
            List<SingerHelper> Lists = new List<SingerHelper>();
            total = 0;
            try
            {
                if (filter != null)
                {
                    if (filter.PageSize > 0 && filter.CurrentPage > 0)
                    {
                        StringBuilder sbSQL = new StringBuilder();
                        List<SqlParameter> listParam = new List<SqlParameter>();
                        sbSQL.Append("select top ");
                        sbSQL.Append(filter.PageSize);
                        sbSQL.Append(" * from (select count(1) over() as totalnumber, ROW_NUMBER() over(order by A.SingerId) as rownumber,A.SingerId,A.SingerName,A.SingerAge,A.SingerSex,A.SingerStyle,A.SingerCountry,A.SingerRank,A.SingerBir,A.SingerDesc,A.SingerPictrue,B.CountryName   from North_Singer A left join North_Country B on A.SingerCountry=B.CountryId where  1 = 1  ");
                        if (!string.IsNullOrEmpty(filter.singer))
                        {
                            sbSQL.Append(" and B.SingerName like '%' + @Singername + '%'");
                            SqlParameter param = new SqlParameter("@Singername", SqlDbType.NVarChar);
                            param.Value = filter.singer;
                            listParam.Add(param);
                        }
                        sbSQL.Append(") WTX where rownumber > (");
                        sbSQL.Append((filter.CurrentPage - 1) * filter.PageSize);
                        sbSQL.Append(")");
                        using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sbSQL.ToString(), listParam.ToArray()))
                        {
                            while (dr.Read())
                            {
                                int iIndex = 0;
                                SingerHelper list = new SingerHelper();                          
                                list.Singer = new ModelsHelper().GetNorthSingerModels(dr);
                                list.Country = new ModelsHelper().GetNorthCountryModels(dr);
                                Lists.Add(list);
                                if (iIndex == 0)
                                {
                                    total = DatabaseFactory.GetDatabase().GetInt(dr, "totalnumber");
                                }
                                iIndex++;
                            }
                        }

                    }

                }

            }
            catch (Exception ex)
            {

            }
            return Lists;
        }

        public List<North_Singer> GetAllSIngers()
        {
            List<North_Singer> lists = new List<North_Singer>();
            try
            {
                string sql = "select * from North_Singer where IsVaild='N'";
                using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
                {
                    while (dr.Read())
                    {
                        North_Singer list = new ModelsHelper().GetNorthSingerModels(dr);
                        lists.Add(list);
                    }
                }
            }
            catch (Exception ex)
            {

            }        
            return lists;
        }
        public List<North_Singer> GetAllBaiduSingers()
        {
            List<North_Singer> lists = new List<North_Singer>();
            try
            {
                string sql = "select * from North_Singer where IsVaildB='N'";
                using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
                {
                    while (dr.Read())
                    {
                        North_Singer list = new ModelsHelper().GetNorthSingerModels(dr);
                        lists.Add(list);
                    }
                }
            }
            catch (Exception ex)
            {

            }
            return lists;
        }
        public int ModifySingerInfo(North_Singer singer,string name)
        {
            int bresult = 0;
            try
            {
                string sql = $"update North_Singer set IsVaild='Y', SingerDesc='{singer.SingerDesc.Replace("'"," ")}',SingerPictrue='{singer.SingerPictrue}' where SingerName='{name}'";
                bresult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {
            }
            return bresult;
        }
        public int ModifyBaiduSingerInfo(North_Singer singer, string name)
        {
            if (string.IsNullOrEmpty(singer.SingerBir))
            {
                singer.SingerBir = null;
            }
            int bresult = 0;
            try
            {
                string sql = $"update North_Singer set IsVaildB='Y', SingerSex='{singer.SingerSex}',SingerBir='{singer.SingerBir}',SingerFName='{singer.SingerFName}',Company='{singer.Company}' where SingerName='{name}'";
                bresult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {
            }
            return bresult;
        }
        public North_Singer GetSingerInfo(string name)
        {
            North_Singer singer = null;
            try
            {
                string sql = $@"select * from North_Singer where SingerName like '%{name}%' ";
                using (SqlDataReader dr = DatabaseFactory.GetDatabase().ExecuteDataReaderSQL(sql))
                {
                    while (dr.Read())
                    {
                        singer = new North_Singer();
                        singer = new ModelsHelper().GetNorthSingerModels(dr);
                    }
                }
            }
            catch (Exception)
            {

                throw;
            }
            return singer;
        }

        public int UpdateSingerInfo(North_Singer singer)
        {
          
            int bResult = 0;
            try
            {
                string sql = $"update North_Singer set SingerDesc='{singer.SingerDesc}',SingerPictrue='{singer.SingerPictrue}',SingerAge='{singer.SingerAge}',SingerName='{singer.SingerName}'," +
                    $"SingerSex='{singer.SingerSex}',SingerCountry='{singer.SingerCountry}',SingerBir='{singer.SingerBir}' where SingerId='{singer.SingerId}'";
                bResult = DatabaseFactory.GetDatabase().ExecNonQuerySQL(sql);
            }
            catch (Exception)
            {
            }
            return bResult;
        }
    }
}
